At 12:52 +0200 on 27/01/1999, You wrote:
> For instance I have:
> create table companies(
> id serial primary key,
> ...
> );
> create table ccats(
> cid integer not null references companies(id),
> catid integer not null references categories(id)
> );
>
> What do I have to put instead of ??:
> insert into companies values (??, ...);
> insert into ccats (??, 5);
> ...
> insert into ccats (??, 7);
Serial is in fact an integer, but it gets its default value from a sequence
generator.
As a general rule, don't use INSERT statement without saying each of the
fields to which you want to enter a value. So, instead of using
INSERT INTO companies VALUES (....); -- Bad
Use
INSERT INTO companies (id, field2, field3...) VALUES (....); -- Better
Now it's very simple. All you have to do is not insert anything into the ID
field:
INSERT INTO companies (field2, field3...) VALUES (...); -- Best.
Now, once you have inserted a row into the companies table, a number was
generated for it automatically. As long as you are still connected to the
database in the same connection, you can access this number via the
function currval(); This function needs the name of the sequence generator
as a parameter. To know the name of the sequence generator, do a \d after
you create the companies table. For example, currval( 'id_seq' ) will give
you the last number generated in the current session for the sequence
generator 'id_seq'.
So, the inserts for the ccats table become:
INSERT INTO ccats (cid, catid) VALUES (currval( 'name of seq' ), 5 );
Herouth
--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma
